from django.contrib import messages
from django.http import HttpResponseRedirect, HttpResponse
from django.shortcuts import render, redirect
from pymysql import Connect
import pandas as pd
import json
import jieba.analyse
from keshisizhang.connect import connect


conn = connect()
#折线图
def f1():
    # ..............................................折线图
    cur = conn.cursor()
    cur.execute(
        "select sum(confirmed_add), update_time from covid_data  where update_time!='2020-02-13'  group by update_time")
    result = cur.fetchall()
    import pandas as pd
    df_result = pd.DataFrame(list(result), columns=["confirmed_add", "update_time"])
    date = df_result["add"] = df_result["confirmed_add"]
    date1 = df_result["update_time"]
    date = list(date)
    at = []
    for i in date:
        # print(date.index(i) + 1, i)
        at.append(int(i))
    op = []
    date1 = list(date1)
    for j in date1:
        pp = str(j).split("-")
        nt = int(pp[2])
        op.append(str(nt))
    return op,at
#散点图..........v
def f2():
    # ...................................................散点图
    sql = """SELECT  value,sum(confirmed_count) FROM migrate_data, covid_data
                                       where update_time='2020-02-29' and target_province_name=province_name and source_province_name='湖北省'
                                       group by value """
    cur = conn.cursor()
    cur.execute(sql)  # 执行sql语句
    datax_y = cur.fetchall()
    data_x = []
    data_y = []

    v = []
    for i in datax_y:
        data_x.append(i[0])
        data_y.append(int(i[1]))

    for i in range(len(data_x)):
        k = []
        k.append(data_x[i])
        k.append(data_y[i])
        v.append(k)
    return v
#仪表盘..........dead_rate
def f3():
    sql = """select sum(dead_count) from covid_data where update_time='2020-02-29 '   """
    cur = conn.cursor()
    cur.execute(sql)  # 执行sql语句
    data = cur.fetchall()
    die = data[0][0]
    # for i in data:
    data1 = pd.read_sql("""select sum(confirmed_count)  from covid_data where update_time='2020-02-29 '     """"", conn)
    confirm = int(data1["sum(confirmed_count)"])  # 确诊数
    dead_rate = round(round(die / confirm, 4) * 100, 2)  # 死亡率
    return dead_rate
#饼图.....data_bin
def f4():
    sql = """select news_source,count(*) sum_news from news_info group by news_source"""
    cur = conn.cursor()
    cur.execute(sql)  # 执行sql语句
    data1 = cur.fetchall()
    name = ["name"]
    value = ["value"]
    data_bin = []
    for k in data1:
        c1 = dict(zip(name, [k[0]]))  # 列表合并为键值对
        d1 = dict(zip(value, [k[1]]))
        e1 = {}
        e1.update(d1)  # 将键值对加到字典中
        e1.update(c1)
        data_bin.append(e1)
    return data_bin
#河流.............list2
def f5():
    sql = """select date_format(publish_time,'%Y-%m-%d') time ,news_source,count(*) sum_news from news_info
                where  publish_time >="2020-02-01 00:00:01" and publish_time <="2020-02-29 23:59:59" group by  time ,news_source order by time"""
    cur = conn.cursor()
    cur.execute(sql)
    data1 = cur.fetchall()
    list2 = []
    for i in data1:
        list1 = []
        list1.append(i[0])
        list1.append(i[2])
        list1.append(i[1])
        list2.append(list1)
    return list2
#词云图..............word1
def f6():
    sql = """ select news_summary from news_info"""
    cur = conn.cursor()
    cur.execute(sql)  # 执行sql语句

    data1 = cur.fetchall()
    str1 = ""
    for i in data1:
        p = str(i).replace('年', '1').replace('月', '2').replace('日', '3')
        str1 += str(p)
    import re
    temp = re.sub('[\d]', '', str1)  # [0-9]
    word = jieba.analyse.extract_tags(temp, topK=100, withWeight=True)
    word1 = []
    for i in word:
        name = ["name"]
        value = ["value"]
        a = [i[0]]
        b = [i[1]]
        c = dict(zip(name, a))  # 列表合并为键值对
        d = dict(zip(value, b))
        e = {}
        e.update(c)  # 键值对加入字典
        e.update(d)
        word1.append(e)
    return word1
#迁徙图.............map5，map3，cured
def f7():
    sql_map = """ select longitude,latitude,value from
               (select * from area_china where level=1 and name not in("湖北省","香港特别行政区","澳门特别行政区","台湾省")) a join 
               ( select * from migrate_data where source_province_name="湖北省") b on a.name=b.target_province_name """
    cur = conn.cursor()
    cur.execute(sql_map)  # 执行sql语句
    data1 = cur.fetchall()
    sql_map1 = """ select longitude,latitude from area_china where level=1 and name ="湖北省" """
    cur.execute(sql_map1)  # 执行sql语句
    data2 = cur.fetchall()
    hu_bei = list(data2[0])  # 湖北坐标
    name = ["coords"]
    name1 = ["value"]
    map3 = []
    map5 = []
    for i in data1:
        map1 = []
        map2 = []
        map1.append(hu_bei)
        map1.append(list(i[:2]))  # 各省坐标
        map2.append(map1)
        c = dict(zip(name, map2))
        map3.append(c)
        map4 = []
        map4.append(list(i))
        c1 = dict(zip(name1, map4))
        map5.append(c1)
    # *********************************************************治愈数
    data1 = pd.read_sql("""select sum(cured_count) total1 from covid_data where update_time='2020-02-29 '
                                """"", conn)
    cured = int(data1["total1"])
    return map3,map5,cured
#地图.............data_map
def f8():
    sql = """select province_name,sum(confirmed_count) total from covid_data where update_time="2020-02-29" group by province_name"""
    cur = conn.cursor()
    cur.execute(sql)  # 执行sql语句
    map1 = cur.fetchall()
    data_map = []
    for i in map1:
        name = ["name"]
        value = ["value"]
        a = i[0]
        if a == "内蒙古自治区":
            a1 = a[:3]
            a2 = [a1]  # 各省确诊值
            b = [int(i[1])]
            c = dict(zip(name, a2))  # 列表合并为键值对
            d = dict(zip(value, b))  # 列表合并为键值对
            e = {}
            e.update(c)  # 将键值对加到字典中
            e.update(d)
            data_map.append(e)
        elif a == "黑龙江省":
            a1 = a[:3]
            a2 = [a1]
            b = [int(i[1])]
            c = dict(zip(name, a2))
            d = dict(zip(value, b))
            e = {}
            e.update(c)
            e.update(d)
            data_map.append(e)
        else:
            a1 = a[:2]
            a2 = [a1]
            b = [int(i[1])]
            c = dict(zip(name, a2))
            d = dict(zip(value, b))
            e = {}
            e.update(c)
            e.update(d)
            data_map.append(e)
    return data_map